package com.citycloud.ccuap.tc.sync.mapper;



import com.citycloud.ccuap.tc.sync.dto.DepartmentListDto;

import com.citycloud.ccuap.tc.sync.dto.DepartmentQuery;
import com.citycloud.ccuap.tc.sync.dto.DepartmentSelectedCountDto;
import com.citycloud.ccuap.tc.sync.dto.DepartmentTreeDto;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AspectDepartmentMapper {

    @SelectProvider(type = SqlProvider.class, method = "getTotalCount")
    int getTotalCount();

    @SelectProvider(type = SqlProvider.class, method = "getDepartmentTree")
    List<DepartmentTreeDto> getDepartmentTree(DepartmentQuery page);

    @SelectProvider(type = SqlProvider.class, method = "checkIsParent")
    boolean checkIsParent(Long projectId);

    @SelectProvider(type = SqlProvider.class, method = "countSelectedDepartment")
    DepartmentSelectedCountDto countSelectedDepartment(Long projectId, String fullIdPath);

    @SelectProvider(type = SqlProvider.class, method = "getDeleteRelId")
    List<Long> getDeleteRelId(Long projectId, int limit);

    @DeleteProvider(type = SqlProvider.class, method = "deleteByRelId")
    void deleteByRelId(String relIds);

    @SelectProvider(type = SqlProvider.class, method = "getInsertList")
    List<Long> getInsertList(String deptIds);

    @InsertProvider(type = SqlProvider.class, method = "batchInsert")
    void batchInsert(String values);

    @UpdateProvider(type = SqlProvider.class, method = "updateFullPath")
    void updateFullPath(Long deptId);

    @UpdateProvider(type = SqlProvider.class, method = "completeFullPath")
    void completeFullPath(Long deptId);

    @UpdateProvider(type = SqlProvider.class, method = "updateFullIdPath")
    void updateFullIdPath(Long deptId);

    @UpdateProvider(type = SqlProvider.class, method = "completeFullIdPath")
    void completeFullIdPath(Long deptId);

    @SelectProvider(type = SqlProvider.class, method = "getImportDeptIdsTotalCount")
    int getImportDeptIdsTotalCount(Long projectId);

    @SelectProvider(type = SqlProvider.class, method = "getImportDeptIds")
    List<String> getImportDeptIds(DepartmentQuery page);

    @SelectProvider(type = SqlProvider.class, method = "getImportDepartmentList")
    List<DepartmentListDto> getImportDepartmentList(String deptIds);

    @DeleteProvider(type = SqlProvider.class, method = "deleteDirtyData4SysProjectDept")
    void deleteDirtyData4SysProjectDept();


    class SqlProvider {

        public String getTotalCount() {
            return "SELECT COUNT(1) FROM sys_deptment t WHERE t.project_id IS NULL ";
        }

        public String getDepartmentTree(DepartmentQuery page) {
            return "SELECT t.dept_id AS deptId, t.dept_name AS deptName, t.pid, t.full_path AS fullPath, t.full_id_path AS fullIdPath, IF(rel.rel_id IS NULL,0,1) AS granted FROM sys_deptment t " +
                    " LEFT JOIN sys_project_dept rel ON t.dept_id = rel.dept_id AND rel.project_id = " + page.getProjectId() + " WHERE t.project_id IS NULL ORDER BY t.full_path, t.dept_id LIMIT " + page.getOffset() + " , " + page.getLimit();
        }

        public String checkIsParent(Long projectId) {
            return "SELECT COUNT(1)>0 FROM sys_deptment WHERE pid = " + projectId;
        }

        public String countSelectedDepartment(Long projectId, String fullIdPath) {
            return "SELECT COUNT(dept.dept_id) AS total, COUNT(rel.rel_id) AS selected FROM sys_deptment dept " +
                    "LEFT JOIN sys_project_dept rel ON dept.dept_id = rel.dept_id AND rel.project_id = " + projectId +
                    " WHERE CONCAT(dept.full_id_path,'/') like '" + fullIdPath + "/%'";
        }

        public String getDeleteRelId(Long projectId, int limit) {
            return "SELECT rel.rel_id FROM ( SELECT dept_id FROM sys_deptment WHERE project_id IS NULL ORDER BY full_path, dept_id LIMIT " + limit + ") t " +
                    "LEFT JOIN sys_project_dept rel ON t.dept_id = rel.dept_id " +
                    "WHERE rel.project_id = " + projectId;
        }

        public String deleteByRelId(String relIds) {
            return "DELETE FROM sys_project_dept WHERE rel_id IN (" + relIds + ")";
        }

        public String getInsertList(String deptIds) {
            return "SELECT DISTINCT t2.dept_id FROM sys_deptment t " +
                    "LEFT JOIN sys_deptment t2 ON CONCAT(t2.full_id_path,'/') like CONCAT(t.full_id_path,\"/%\") " +
                    "WHERE t2.dept_id IS NOT NULL AND t2.project_id IS NULL AND t.dept_id IN (" + deptIds + ")";
        }

        public String batchInsert(String values) {
            return "INSERT INTO sys_project_dept (project_id, dept_id) VALUES " + values;
        }

        public String updateFullPath(Long deptId) {
            String sql = "UPDATE sys_deptment l4 " +
                    "LEFT JOIN sys_deptment l3 ON l4.pid = l3.dept_id " +
                    "LEFT JOIN sys_deptment l2 ON l3.pid = l2.dept_id " +
                    "LEFT JOIN sys_deptment l1 ON l2.pid = l1.dept_id " +
                    "SET l4.full_path = CONCAT_WS('/', CONCAT('{', l1.pid, '}'), l1.dept_name, l2.dept_name, l3.dept_name, l4.dept_name) ";
            if (deptId != null) {
                sql = sql + "WHERE l4.dept_id = " + deptId;
            }
            return sql;
        }

        public String completeFullPath(Long deptId) {

            String sql = "UPDATE sys_deptment l4 " +
                    "LEFT JOIN sys_deptment l3 ON substring_index(substring_index(l4.full_path, '}/', 1), '{', -1) = l3.dept_id " +
                    "LEFT JOIN sys_deptment l2 ON l3.pid = l2.dept_id " +
                    "LEFT JOIN sys_deptment l1 ON l2.pid = l1.dept_id " +
                    "SET l4.full_path = CONCAT_WS('/', CONCAT('{', l1.pid, '}'), l1.dept_name, l2.dept_name, l3.dept_name, substring_index(l4.full_path, '}/', -1)) " +
                    "WHERE l4.full_path like '{%' ";
            if (deptId != null) {
                sql = sql + "AND l4.dept_id = " + deptId;
            }
            return sql;
        }


        public String updateFullIdPath(Long deptId) {

            String sql = "UPDATE sys_deptment l4 " +
                    "LEFT JOIN sys_deptment l3 ON l4.pid = l3.dept_id " +
                    "LEFT JOIN sys_deptment l2 ON l3.pid = l2.dept_id " +
                    "LEFT JOIN sys_deptment l1 ON l2.pid = l1.dept_id " +
                    "SET l4.full_id_path = CONCAT_WS('/', CONCAT('{', l1.pid, '}'), l1.dept_id, l2.dept_id, l3.dept_id, l4.dept_id) ";
            if (deptId != null) {
                sql = sql + "WHERE l4.dept_id = " + deptId;
            }
            return sql;
        }

        public String completeFullIdPath(Long deptId) {

            String sql = "UPDATE sys_deptment l4 " +
                    "LEFT JOIN sys_deptment l3 ON substring_index(substring_index(l4.full_id_path, '}/', 1), '{', -1) = l3.dept_id " +
                    "LEFT JOIN sys_deptment l2 ON l3.pid = l2.dept_id " +
                    "LEFT JOIN sys_deptment l1 ON l2.pid = l1.dept_id " +
                    "SET l4.full_id_path = CONCAT_WS('/', CONCAT('{', l1.pid, '}'), l1.dept_id, l2.dept_id, l3.dept_id, substring_index(l4.full_id_path, '}/', -1)) " +
                    "WHERE l4.full_id_path like '{%' ";
            if (deptId != null) {
                sql = sql + "AND l4.dept_id = " + deptId;
            }
            return sql;
        }


        public String getImportDeptIdsTotalCount(Long projectId) {
            return  "SELECT count(1)  from sys_deptment t LEFT JOIN   " +
                    "(select dept_id from sys_project_deptment_rel WHERE project_id=" + projectId +") as rel " +
                    "on rel.dept_id=t.dept_id WHERE rel.dept_id is null and t.data_status=1 AND t.project_id IS NULL";
//            return "SELECT count(1) FROM sys_project_dept rel " +
//                    "LEFT JOIN sys_deptment t ON rel.dept_id = t.dept_id " +
//                    "LEFT JOIN sys_project_deptment_rel imp ON rel.project_id = imp.project_id AND rel.dept_id = imp.dept_id " +
//                    "WHERE rel.project_id = " + projectId +
//                    " AND t.dept_id IS NOT NULL " + //排除脏数据
//                    " AND t.project_id IS NULL " + //再排除一个脏数据
//                    " and t.data_status =1 " +
//                    " AND imp.dept_id IS NULL ";

        }

        public String getImportDeptIds(DepartmentQuery page) {
           return "SELECT t.full_id_path from sys_deptment t LEFT JOIN   " +
                   "(select dept_id from sys_project_deptment_rel WHERE project_id=" + page.getProjectId() +") as rel " +
                   "on rel.dept_id=t.dept_id WHERE rel.dept_id is null and t.data_status=1 AND t.project_id IS NULL "+
                   "ORDER BY t.full_path, t.dept_id " +
                   "LIMIT " + page.getOffset() + " , " + page.getLimit();
//            return "SELECT t.full_id_path FROM sys_project_dept rel " +
//                    "LEFT JOIN sys_deptment t ON rel.dept_id = t.dept_id " +
//                    "LEFT JOIN sys_project_deptment_rel imp ON rel.project_id = imp.project_id AND rel.dept_id = imp.dept_id " +
//                    "WHERE rel.project_id = " + page.getProjectId() +
//                    " AND t.dept_id IS NOT NULL " + //排除脏数据
//                    " AND t.project_id IS NULL " + //再排除一个脏数据
//                    " AND imp.dept_id IS NULL " +
//                    " and t.data_status =1 " +
//                    "ORDER BY t.full_path, t.dept_id " +
//                    "LIMIT " + page.getOffset() + " , " + page.getLimit();
        }

        public String getImportDepartmentList(String deptIds) {
            return "SELECT t.dept_id AS id, t.pid, t.dept_name AS name, t2.dept_id IS NOT NULL AS open FROM sys_deptment t " +
                    "LEFT JOIN sys_deptment t2 ON t.dept_id = t2.pid " +
                    "WHERE t.data_status = 1 and t.dept_id IN (" + deptIds + ") GROUP BY t.dept_id ";
        }

        public String deleteDirtyData4SysProjectDept() {
            return "DELETE rel FROM sys_project_dept rel LEFT JOIN sys_deptment t ON rel.dept_id = t.dept_id WHERE t.dept_id IS NULL";
        }
    }

}
